package com.example.myapplication.utils;

import android.annotation.SuppressLint;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.myapplication.R;
import com.example.myapplication.WordApp;
import com.example.myapplication.model.Word;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class SQLiteHelper {

    private static SQLiteHelper mInstance;

    private SQLiteDatabase  mDataBase;

    private SQLiteHelper() {}


    public static SQLiteHelper getInstance()
    {
        if(mInstance == null) {
            mInstance = new SQLiteHelper();
        }
        return mInstance;
    }

    private void openDataBase() {
        if(mDataBase != null && mDataBase.isOpen()) {
            return;
        }
        String dst =
            WordApp.AppContext.getExternalFilesDir("").getAbsolutePath();
        dst += "/words.db";

        InputStream ins =
            WordApp.AppContext.getResources().openRawResource(R.raw.words);

        try {
            FileOutputStream fos = new FileOutputStream(dst);
            byte[] buf = new byte[ins.available()];

            int length = 0;
            do {
                if (length > 0) {
                    fos.write(buf, 0, length);
                }
                length = ins.read(buf);
            } while (length > 0);
            fos.flush();
            fos.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        mDataBase = SQLiteDatabase.openDatabase(dst, null, SQLiteDatabase.OPEN_READONLY);
    }

    @SuppressLint("Range")
    public List<Word> query(String db, int start, int end) {
        if(end < start) {
            //交换 end 与 start
            start = start + end;
            end = start - end;
            start = start - end;
        }
        List<Word> result = new ArrayList<>();
        openDataBase();
        String  sql = "SELECT * FROM `" + db + "` WHERE id >= " + start + " AND id <= " + end;

        Cursor cursor = mDataBase.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            //TODO 把查询到的数据保存下来
            Word word = new Word();
            word.id = cursor.getInt( cursor.getColumnIndex("id") );
            word.en = cursor.getString( cursor.getColumnIndex("en") );
            word.vi = cursor.getString( cursor.getColumnIndex("vi") );
            word.vt = cursor.getString( cursor.getColumnIndex("vt") );
            word.prep = cursor.getString( cursor.getColumnIndex("prep") );
            word.pron = cursor.getString( cursor.getColumnIndex("pron") );
            word.abbr = cursor.getString( cursor.getColumnIndex("abbr") );
            word.adj = cursor.getString( cursor.getColumnIndex("adj") );
            word.adv = cursor.getString( cursor.getColumnIndex("adv") );
            word.art = cursor.getString( cursor.getColumnIndex("art") );
            word.n = cursor.getString( cursor.getColumnIndex("n") );
            word.conj = cursor.getString( cursor.getColumnIndex("conj") );
            word.other = cursor.getString( cursor.getColumnIndex("other") );
            result.add(word);
        }
        return result;
    }

}
